package com.lagou.dao;

import com.lagou.entity.Dept;
import com.lagou.entity.Employee;
import com.lagou.utils.DruidUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import java.sql.SQLException;
import java.util.List;

public class EmployeeDao {
    /**
     * 查询所有的员工信息 (不包含没有部门的员工)。
     * 用list集合进行封装符合查询条件的员工
     */
    public List<Employee> getAllEmployeeWithDept() throws SQLException {
        //创建QueryRunner对象 自动
        QueryRunner qr = new QueryRunner(DruidUtils.getDataSource());
        //编写SQL
        String sql = "select * from employee where did is not null";
        //执行查询操作
        List<Employee> query = qr.query(sql, new BeanListHandler<Employee>(Employee.class));
        return  query;
    }

    /**
     * 查询每个员工的 姓名, 薪资 和 所属部门名称
     * 用LIST集合进行疯涨
     */
    public List<Employee> getAllEmployee() throws SQLException {
        QueryRunner qr = new QueryRunner(DruidUtils.getDataSource());
        //编写SQL
        String sql = "select * from employee";
        List<Employee> query = qr.query(sql, new BeanListHandler<Employee>(Employee.class));
        //遍历部门名称
        for (Employee employee : query) {
            employee.setDept(findDeptById(employee.getDid()));
        }
        return query;
    }

        //根据id获取部门信息
    public Dept findDeptById(int id) throws SQLException {
        QueryRunner qr = new QueryRunner(DruidUtils.getDataSource());
        String sql = "select * from dept where id = ?";
        //查询部门信息并返回结果
        Dept query = qr.query(sql, new BeanHandler<Dept>(Dept.class), id);
        //如果部门为空则返回不包含任何信息的部门
        if (query == null){
            query = new Dept();
        }
        return query;
    }


}
